'--------------------------------------------------------
' 测试方法:
' 需要打开 src/test/sql/sqltranslate/test.xlsx 来进行测试
'--------------------------------------------------------

'--------------------------------------------------------
' 测试缓存
'--------------------------------------------------------
Private cacheEm As SeparateOneSheetEntityManager
Private cacheTranslator As SqlTranslator

'--------------------------------------------------------
' 执行所有测试用例
'--------------------------------------------------------
Private Sub test_SQLTranslator_ALL_case()
    ' -------------------------------
    ' select + 基础测试
    ' -------------------------------
    ' 其他测试: 空 sql
    test_SQLTranslator_empty_sql
    ' 测试: vbCrLf + vbLf + vbTab 可以被正确识别
    test_SQLTranslator_can_cut_vbCrLf_vblf_vbTab
    ' -------------------------------
    ' select + 基础测试
    ' -------------------------------
    ' select 测试: 基础 select 转换
    test_SQLTranslator_select_base
    ' select 测试: 子查询转换
    test_SQLTranslator_select_subSelect_in_condition
    ' select 测试: 筛选项运算符结合列转换
    test_SQLTranslator_select_colInfo_operator_compositeCol
    ' select 测试: 筛选项运算符结合列 + case 转换
    test_SQLTranslator_select_colInfo_operator_case_compositeCol
    ' select 测试: 子查询作为筛选列
    test_SQLTranslator_select_subSelect_is_colInfo
    ' select 测试: between 作为为筛选列
    test_SQLTranslator_select_between_is_colInfo
    ' select 测试: in 作为为筛选列
    test_SQLTranslator_select_in_is_colInfo
    ' select 测试: exist 作为为筛选列
    test_SQLTranslator_select_exists_is_colInfo
    ' select 测试: 多个 exists 列组合成筛选列
    test_SQLTranslator_select_multi_exists_is_colInfo
    ' select 测试: cast 作为筛选列
    test_SQLTranslator_select_cast_is_colInfo
    ' select 测试:  exists 作为条件
    test_SQLTranslator_select_multi_exists_is_condition
    ' select 测试: in 作为为筛选列
    test_SQLTranslator_select_in_is_condition
    ' select 测试: between 作为为筛选列
    test_SQLTranslator_select_between_is_condition
    ' select 测试: 基础 select union -- 包含 ()
    test_SQLTranslator_select_union_hasParenthesis
    ' select 测试: 基础 select union -- 不包含 ()
    test_SQLTranslator_select_union_noParenthesis
    ' select 测试: 基础 select union -- 开头包含 () + union
    test_SQLTranslator_select_union_parenthesis_in_start
    ' select 测试: 基础 select union 子查询中包含子查询
    test_SQLTranslator_select_union_with_subselect
    ' select 测试:
    ' 1. any 函数测试
    ' 2. 对于未声明别名的表，其列前面的表名被清空
    test_SQLTranslator_select_any_And_clear_no_aliasTable_of_colInfo
    ' select 测试: 筛选项中的 * ASTERISK
    test_SQLTranslator_select_asterisk_in_colInfo
    ' select 测试: 筛选项中【没有参数的函数】
    test_SQLTranslator_select_colInfo_func_no_params
    ' select 测试: 筛选项中的正号、负号
    test_SQLTranslator_select_colInfo_num_sign
    ' select 测试: tips 基础测试
    test_SQLTranslator_select_tips_base
    ' select 测试: 子查询作为 from 表定义
    test_SQLTranslator_select_subSelect_is_form_table_2
    ' select 测试: 字面量列作为筛选项
    ' 数字、字符串、true、false、null 作为列
    test_SQLTranslator_select_literal_colInfo
    ' select 测试: 多表测试: 真实表 + 子查询表
    test_SQLTranslator_ASTERISK
    ' select 测试: from + partition 包含 ()
    test_SQLTranslator_select_partition_with_Parenthesis
    ' select 测试: from + partition 不包含 ()
    test_SQLTranslator_select_partition_no_Parenthesis
    ' select 测试: 获取 union 中的列名
    test_SQLTranslator_select_get_union_colname
    ' -------------------------------
    ' update 测试
    ' -------------------------------
    ' update 测试: 基础组成结构测试
    test_SQLTranslator_update_base
    ' update 测试: 表别名测试
    test_SQLTranslator_update_table_aliasName
    ' update 测试: 多表测试: 真实表 + 子查询表
    test_SQLTranslator_update_multi_table

    ' -------------------------------
    ' delete 测试
    ' -------------------------------
    ' delete 测试: 单表删除测试
    test_SQLTranslator_delete_single_table

    ' -------------------------------
    ' insert 测试
    ' -------------------------------
    ' insert 测试: 基础测试
    test_SQLTranslator_insert_base
    ' insert 测试: 全关键字测试
    test_SQLTranslator_insert_all_key
    ' insert 测试: ON DUPLICATE KEY UPDATE
    test_SQLTranslator_insert_on_duplicate
    ' insert 测试: 子程序测试
    test_SQLTranslator_insert_subSelect

    ' -------------------------------
    ' truncate 测试
    ' -------------------------------
    ' truncate 测试
    test_SQLTranslator_truncate_base

    ' -------------------------------
    ' with 测试
    ' -------------------------------
    ' with 测试: 基础测试
    test_SQLTranslator_with_base
    ' with 测试: with 中包含 union
    test_SQLTranslator_with_from_union
    ' with 测试: with 语句中的子查询中明确声明各个列的名称
    test_SQLTranslator_with_define_colname
    ' with 测试: union 中的每个子查询中各自包含一个/多个 with
    test_SQLTranslator_with_used_in_union_every_subSelect
    ' with 测试: with + union 嵌套的组合
    test_SQLTranslator_with_nest_union_nest
    ' with 测试: 多个sql 中的 with
    test_SQLTranslator_with_multi_sql
    ' with 测试: with 的子查询中的列全部是字母量列
    test_SQLTranslator_with_Literal_col
    ' with 测试: with + update
    test_SQLTranslator_with_in_update
    ' with 测试: with + delete
    test_SQLTranslator_with_in_delete
    ' with 测试: with 在 where 的子查询中
    test_SQLTranslator_with_in_where_subselect
    ' with 测试: with 在 join 的子查询中
    test_SQLTranslator_with_in_join_subselect
    ' with 测试: with 在 join 子查询表中
    test_SQLTranslator_with_in_join_table
    ' with 测试: with 在 from 子查询表中
    test_SQLTranslator_with_in_from_table
    ' with 测试: with 在 join 的子查询中
    test_SQLTranslator_with_in_exists
    ' with 测试: with 在筛选列的子查询中
    test_SQLTranslator_with_in_subselect_col
    ' with 测试: with 在 in 条件的子查询中
    test_SQLTranslator_with_inCondition

    ' -------------------------------
    ' 非标准 sql 测试
    ' -------------------------------
    ' 非标准 sql 测试: 基础转换
    test_SQLTranslator_AbnormalSnippets_base
    ' 非标准 sql 测试: 从 join 开始
    test_SQLTranslator_AbnormalSnippets_join_start

    ' -------------------------------
    ' tips 测试
    ' -------------------------------
    ' tips 测试: 只有表名，不设置表别名
    test_SQLTranslator_tips_no_table_aliasName
    ' sql 常量测试 测试: 基础识别测试
    test_SQLTranslator_sqlConst_base
End Sub

'--------------------------------------------------------
' test common
'--------------------------------------------------------
' 创建用于测试的 entity 管理器
Private Function createEm() As ISqlEntityManager
    If cacheEm Is Nothing Then
        Set cacheEm = New SeparateOneSheetEntityManager

        ' 1. 需要提供两个管理 sheet 对象
        Dim entityInfoSheet As WorkSheet
        Set entityInfoSheet = Sheets("entityList")
        Dim itemInfoSheet As WorkSheet
        Set itemInfoSheet = Sheets("entityItems")

        ' 2. 执行初始化
        cacheEm.Init _
            entityInfoSheet, _
            itemInfoSheet, _
            entityInfoStartRow:= 4, _
            entityLogicNameCol:= "C", _
            entityPhyNameCol:= "D", _
            itemInfoStartRow:= 4, _
            itemPhyNameCol:= "D", _
            itemLogicNameCol:= "C", _
            itemEntityLogicNameCol:= "B"
    End If
    Set createEm = cacheEm
End Function

' 创建 sql 转换器
Private Function createSQLTranslator() As SqlTranslator
    If cacheTranslator Is Nothing Then
        ' Dim em As ISqlEntityManager
        ' Set em = createEm
        Set cacheTranslator = New SqlTranslator
        cacheTranslator.Init createEm
    End If

    Set createSQLTranslator = cacheTranslator
End Function

'========================================================
'========================================================
' 其他测试
'========================================================
'========================================================
' 其他测试: 空 sql
Private Sub test_SQLTranslator_empty_sql()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = ""

    Dim exceptedSql as String
    exceptedSql = ""

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' 测试: vbCrLf + vbLf + vbTab 可以被正确识别
Private Sub test_SQLTranslator_can_cut_vbCrLf_vblf_vbTab()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbLf & _
        "left outer join XXXB t2 on " & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbLf & _
        "Where" & vbCrLf & vbTab & vbTab & _
        "    XXXA.AN1 = '123'" & vbLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "Group by XXXA.AN1, XXXB.BN1" & vbLf & _
        "Having XXXA.AN1 > XXXB.BN1" & vbCrLf & _
        "       And XXXB.BN1 between XXXA.AN1 And XXXA.AN2" & vbLf & _
        "Order by XXXA.AN1 desc, XXXB.BN1 asc, XXXB.BN1"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbLf & _
        "left outer join table2 t2 on " & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbLf & _
        "    And t1.apn2 = t2.bpn2" & vbLf & _
        "Where" & vbCrLf & vbTab & vbTab & _
        "    t1.apn1 = '123'" & vbLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "Group by t1.apn1, t2.bpn1" & vbLf & _
        "Having t1.apn1 > t2.bpn1" & vbCrLf & _
        "       And t2.bpn1 between t1.apn1 And t1.apn2" & vbLf & _
        "Order by t1.apn1 desc, t2.bpn1 asc, t2.bpn1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

'========================================================
'========================================================
' select + 基础测试
'========================================================
'========================================================
' select 测试: 基础 select 转换
Private Sub test_SQLTranslator_select_base()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on " & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "Group by XXXA.AN1, XXXB.BN1" & vbCrLf & _
        "Having XXXA.AN1 > XXXB.BN1" & vbCrLf & _
        "       And XXXB.BN1 between XXXA.AN1 And XXXA.AN2" & vbCrLf & _
        "Order by XXXA.AN1 desc, XXXB.BN1 asc, XXXB.BN1"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on " & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "Group by t1.apn1, t2.bpn1" & vbCrLf & _
        "Having t1.apn1 > t2.bpn1" & vbCrLf & _
        "       And t2.bpn1 between t1.apn1 And t1.apn2" & vbCrLf & _
        "Order by t1.apn1 desc, t2.bpn1 asc, t2.bpn1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 子查询转换
Private Sub test_SQLTranslator_select_subSelect_in_condition()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on " & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "left outer join XXXC t3 on " & vbCrLf & _
        "    XXXB.BN1 = XXXC.CN1" & vbCrLf & _
        "    And Not Exists (" & vbCrLf & _
        "        select *" & vbCrLf & _
        "        from XXXD td" & vbCrLf & _
        "        where DN1 = table1.AN1" & vbCrLf & _
        ")" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on " & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "left outer join table3 t3 on " & vbCrLf & _
        "    t2.bpn1 = t3.cpn1" & vbCrLf & _
        "    And Not Exists (" & vbCrLf & _
        "        select *" & vbCrLf & _
        "        from table4 td" & vbCrLf & _
        "        where dpn1 = t1.apn1" & vbCrLf & _
        ")" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 筛选项运算符结合列转换
Private Sub test_SQLTranslator_select_colInfo_operator_compositeCol()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "    (XXXA.AN1 || XXXB.BN1 + XXXB.BN1 And XXXB.BN1 = XXXB.BN2) Or (XXXA.AN1 = XXXB.BN2)" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "    (t1.apn1 || t2.bpn1 + t2.bpn1 And t2.bpn1 = t2.bpn2) Or (t1.apn1 = t2.bpn2)" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 筛选项运算符结合列 + case 转换
Private Sub test_SQLTranslator_select_colInfo_operator_case_compositeCol()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "XXXB.BN1 + (" & vbCrLf & _
        "    Case XXXA.AN1 When '0' Then 10" & vbCrLf & _
        "    when '1' Then 20" & vbCrLf & _
        "    Else XXXA.AN2" & vbCrLf & _
        "    End" & vbCrLf & _
        ") as cx" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "t2.bpn1 + (" & vbCrLf & _
        "    Case t1.apn1 When '0' Then 10" & vbCrLf & _
        "    when '1' Then 20" & vbCrLf & _
        "    Else t1.apn2" & vbCrLf & _
        "    End" & vbCrLf & _
        ") as cx" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 子查询作为筛选列
Private Sub test_SQLTranslator_select_subSelect_is_colInfo()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "(" & vbCrLf & _
        "    select XXXA.AN1 From XXXD t3" & vbCrLf & _
        "    where t3.DN1 = XXXA.AN2" & vbCrLf & _
        ") as cx" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "(" & vbCrLf & _
        "    select t1.apn1 From table4 t3" & vbCrLf & _
        "    where t3.dpn1 = t1.apn2" & vbCrLf & _
        ") as cx" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: between 作为为筛选列
Private Sub test_SQLTranslator_select_between_is_colInfo()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "XXXA.AN1 Between XXXB.BN1 + 10 and XXXB.BN2 as cx" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "t1.apn1 Between t2.bpn1 + 10 and t2.bpn2 as cx" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: in 作为为筛选列
Private Sub test_SQLTranslator_select_in_is_colInfo()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "XXXA.AN1 in( XXXB.BN1 + 10, XXXB.BN2, XXXA.AN2) as cx," & vbCrLf & _
        "XXXA.AN1 as cy" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "t1.apn1 in( t2.bpn1 + 10, t2.bpn2, t1.apn2) as cx," & vbCrLf & _
        "t1.apn1 as cy" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: exist 作为为筛选列
Private Sub test_SQLTranslator_select_exists_is_colInfo()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "not Exists (select XXXA.AN1 from XXXB t2 where XXXB.BN2 = XXXA.AN1) as cx," & vbCrLf & _
        "XXXA.AN1 as cy" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"


    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "not Exists (select t1.apn1 from table2 t2 where t2.bpn2 = t1.apn1) as cx," & vbCrLf & _
        "t1.apn1 as cy" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 多个 exists 列组合成筛选列
Private Sub test_SQLTranslator_select_multi_exists_is_colInfo()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "not Exists (select XXXA.AN1 from XXXB t2 where XXXB.BN2 = XXXA.AN1)" & vbCrLf & _
        "And not Exists (select XXXA.AN1 from XXXB t2 where XXXB.BN2 = XXXA.AN1)" & vbCrLf & _
        "as cx," & vbCrLf & _
        "XXXA.AN1 || not Exists (select XXXA.AN1 from XXXB t2 where XXXB.BN2 = XXXA.AN1) as cz," & vbCrLf & _
        "XXXA.AN1 as cy" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "not Exists (select t1.apn1 from table2 t2 where t2.bpn2 = t1.apn1)" & vbCrLf & _
        "And not Exists (select t1.apn1 from table2 t2 where t2.bpn2 = t1.apn1)" & vbCrLf & _
        "as cx," & vbCrLf & _
        "t1.apn1 || not Exists (select t1.apn1 from table2 t2 where t2.bpn2 = t1.apn1) as cz," & vbCrLf & _
        "t1.apn1 as cy" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: cast 作为筛选列
Private Sub test_SQLTranslator_select_cast_is_colInfo()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "Cast(XXXA.AN1 As Integer) as cx" & vbCrLf & _
        "XXXA.AN1 as cy" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "Cast(t1.apn1 As Integer) as cx" & vbCrLf & _
        "t1.apn1 as cy" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试:  exists 作为条件
Private Sub test_SQLTranslator_select_multi_exists_is_condition()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "XXXA.AN1 as cy" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "    And not Exists (select XXXA.AN1 from XXXB t2 where XXXB.BN2 = XXXA.AN1)" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "    And not Exists (select XXXA.AN1 from XXXB t2 where XXXB.BN2 = XXXA.AN1)" & vbCrLf & _
        "    And XXXA.AN1 || not Exists (select XXXA.AN1 from XXXB t2 where XXXB.BN2 = XXXA.AN1)"


    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "t1.apn1 as cy" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "    And not Exists (select t1.apn1 from table2 t2 where t2.bpn2 = t1.apn1)" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "    And not Exists (select t1.apn1 from table2 t2 where t2.bpn2 = t1.apn1)" & vbCrLf & _
        "    And t1.apn1 || not Exists (select t1.apn1 from table2 t2 where t2.bpn2 = t1.apn1)"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: in 作为为筛选列
Private Sub test_SQLTranslator_select_in_is_condition()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "XXXA.AN1 as cy" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "    And XXXA.AN1 in( XXXB.BN1 + 10, XXXB.BN2, XXXA.AN2)" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "    And XXXA.AN1 in( XXXB.BN1 + 10, XXXB.BN2, XXXA.AN2)"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "t1.apn1 as cy" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "    And t1.apn1 in( t2.bpn1 + 10, t2.bpn2, t1.apn2)" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "    And t1.apn1 in( t2.bpn1 + 10, t2.bpn2, t1.apn2)"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: between 作为为筛选列
Private Sub test_SQLTranslator_select_between_is_condition()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select Distinct" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "    And XXXA.AN1 Between XXXB.BN1 + 10 and XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "    And XXXA.AN1 Between XXXB.BN1 + 10 and XXXB.BN2"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select Distinct" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "    And t1.apn1 Between t2.bpn1 + 10 and t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "    And t1.apn1 Between t2.bpn1 + 10 and t2.bpn2"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 基础 select union -- 包含 ()
Private Sub test_SQLTranslator_select_union_hasParenthesis()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "union all" & vbCrLf & _
        "((" & vbCrLf & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "))"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "union all" & vbCrLf & _
        "((" & vbCrLf & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "))"
    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 基础 select union -- 开头包含 () + union
Private Sub test_SQLTranslator_select_union_parenthesis_in_start()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "((  ((Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'))" & vbCrLf & _
        "union all" & vbCrLf & _
        "((" & vbCrLf & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "))  ))"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "((  ((Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'))" & vbCrLf & _
        "union all" & vbCrLf & _
        "((" & vbCrLf & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "))  ))"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 基础 select union -- 不包含 ()
Private Sub test_SQLTranslator_select_union_noParenthesis()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "union all" & vbCrLf & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "union all" & vbCrLf & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"
    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 基础 select union 子查询中包含子查询
Private Sub test_SQLTranslator_select_union_with_subselect()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "    And XXXA.AN2 = Select MAX(CN1) From XXXC tx where XXXC.CN1 = XXXA.AN1" & vbCrLf & _
        "union all" & vbCrLf & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "    And t1.apn2 = Select MAX(cpn1) From table3 tx where tx.cpn1 = t1.apn1" & vbCrLf & _
        "union all" & vbCrLf & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"
    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub


' select 测试: 基础 select join
Private Sub test_SQLTranslator_select_join()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "right outer join XXXB t3 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "inner join XXXB t4 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "cross join XXXB t5 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t5.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "right outer join table2 t3 on" & vbCrLf & _
        "    t1.apn1 = t3.bpn1" & vbCrLf & _
        "    And t1.apn2 = t3.bpn2" & vbCrLf & _
        "inner join table2 t4 on" & vbCrLf & _
        "    t1.apn1 = t4.bpn1" & vbCrLf & _
        "    And t1.apn2 = t4.bpn2" & vbCrLf & _
        "cross join table2 t5 on" & vbCrLf & _
        "    t1.apn1 = t5.bpn1" & vbCrLf & _
        "    And t1.apn2 = t5.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试:
' 1. any 函数测试
' 2. 对于未声明别名的表，其列前面的表名被清空
Private Sub test_SQLTranslator_select_any_And_clear_no_aliasTable_of_colInfo()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = Any(" & vbCrLf & _
        "       select XXXB.BN2 from XXXB" & vbCrLf & _
        "    )"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = Any(" & vbCrLf & _
        "       select bpn2 from table2" & vbCrLf & _
        "    )"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 筛选项中的 * ASTERISK
Private Sub test_SQLTranslator_select_asterisk_in_colInfo()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select count(*), *, t1.AN1" & vbCrLf & _
        "From XXXA t1"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select count(*), *, t1.apn1" & vbCrLf & _
        "From table1 t1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 筛选项中【没有参数的函数】
Private Sub test_SQLTranslator_select_colInfo_func_no_params()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select count() as cx, cast() as cy" & vbCrLf & _
        "From XXXA t1"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select count() as cx, cast() as cy" & vbCrLf & _
        "From table1 t1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 筛选项中的正号、负号
Private Sub test_SQLTranslator_select_colInfo_num_sign()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select -(+10) As c1, -(+(XXXA.AN1)) As c2, -(+XXXA.AN2) As c3" & vbCrLf & _
        "From XXXA t1"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select -(+10) As c1, -(+(t1.apn1)) As c2, -(+t1.apn2) As c3" & vbCrLf & _
        "From table1 t1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: tips 基础测试
Private Sub test_SQLTranslator_select_tips_base()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "left outer join XXXB t2 on " & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "left outer join table2 t2 on " & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql, "XXXA t1")

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 子查询作为 from 表定义
' 对于子查询部分的列引用，如果不使用子查询表的别名，将无法进行替换
Private Sub test_SQLTranslator_select_subSelect_is_form_table_1()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From (" & vbCrLf & _
        "    select" & vbCrLf & _
        "        XXXA.AN1," & vbCrLf & _
        "        XXXA.AN2," & vbCrLf & _
        "    From XXXA t3" & vbCrLf & _
        "    left outer join XXXB t4" & vbCrLf & _
        "    On XXXB.BN1 = XXXA.AN1" & vbCrLf & _
        "    And XXXB.BN2 = XXXA.AN2" & vbCrLf & _
        ") As t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    ' 对于子查询部分的列引用，如果不使用子查询表的别名，将无法进行替换
    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From (" & vbCrLf & _
        "    select" & vbCrLf & _
        "        t3.apn1," & vbCrLf & _
        "        t3.apn2," & vbCrLf & _
        "    From table1 t3" & vbCrLf & _
        "    left outer join table2 t4" & vbCrLf & _
        "    On t4.bpn1 = t3.apn1" & vbCrLf & _
        "    And t4.bpn2 = t3.apn2" & vbCrLf & _
        ") As t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    XXXA.AN1 = t2.bpn1" & vbCrLf & _
        "    And XXXA.AN2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 子查询作为 from 表定义
Private Sub test_SQLTranslator_select_subSelect_is_form_table_2()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    t1.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From (" & vbCrLf & _
        "    select" & vbCrLf & _
        "        XXXA.AN1," & vbCrLf & _
        "        XXXA.AN2," & vbCrLf & _
        "    From XXXA t3" & vbCrLf & _
        "    left outer join XXXB t4" & vbCrLf & _
        "    On XXXB.BN1 = XXXA.AN1" & vbCrLf & _
        "    And XXXB.BN2 = XXXA.AN2" & vbCrLf & _
        ") As t1" & vbCrLf & _
        "left outer join XXXB t2 on" & vbCrLf & _
        "    t1.AN1 = XXXB.BN1" & vbCrLf & _
        "    And t1.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.AN1 = '123'" & vbCrLf & _
        "    And t1.AN2 = 'zzz'"

    ' 对于子查询部分的列引用，使用子查询表的别名来标识列
    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From (" & vbCrLf & _
        "    select" & vbCrLf & _
        "        t3.apn1," & vbCrLf & _
        "        t3.apn2," & vbCrLf & _
        "    From table1 t3" & vbCrLf & _
        "    left outer join table2 t4" & vbCrLf & _
        "    On t4.bpn1 = t3.apn1" & vbCrLf & _
        "    And t4.bpn2 = t3.apn2" & vbCrLf & _
        ") As t1" & vbCrLf & _
        "left outer join table2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 字面量列作为筛选项
' 数字、字符串、true、false、null 作为列
Private Sub test_SQLTranslator_select_literal_colInfo()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "select 1 as c1, 'abc' as c2, true as c3, false as c4, NULL as c5, t2.BN2" & vbCrLf & _
        "from XXXA t1" & vbCrLf & _
        "inner join XXXB t2" & vbCrLf & _
        "on t1.AN1 = t2.BN1"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "select 1 as c1, 'abc' as c2, true as c3, false as c4, NULL as c5, t2.bpn2" & vbCrLf & _
        "from table1 t1" & vbCrLf & _
        "inner join table2 t2" & vbCrLf & _
        "on t1.apn1 = t2.bpn1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: 多表测试: 真实表 + 子查询表
Private Sub test_SQLTranslator_ASTERISK()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "select" & vbCrLf & _
        "    XXXB.BN1," & vbCrLf & _
        "    XXXC.CN1," & vbCrLf & _
        "    t1.BN2," & vbCrLf & _
        "    t1.CN2" & vbCrLf & _
        "from (" & vbCrLf & _
        "    select * from XXXB t2 inner join XXXC t3 on t2.BN1 = t3.CN1" & vbCrLf & _
        ") as t1"

    ' XXXB.BN1 无法被替换，因为不会深入子查询中搜索表名
    ' XXXC.CN1 无法被替换，因为不会深入子查询中搜索表名
    Dim exceptedSql as String
    exceptedSql = "" & _
        "select" & vbCrLf & _
        "    XXXB.BN1," & vbCrLf & _
        "    XXXC.CN1," & vbCrLf & _
        "    t1.bpn2," & vbCrLf & _
        "    t1.cpn2" & vbCrLf & _
        "from (" & vbCrLf & _
        "    select * from table2 t2 inner join table3 t3 on t2.bpn1 = t3.cpn1" & vbCrLf & _
        ") as t1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: from + partition 包含 ()
Private Sub test_SQLTranslator_select_partition_with_Parenthesis()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' () 能够配对
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "From XXXA t1 Partition ((a1, a2, a3))" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = Any(" & vbCrLf & _
        "       select XXXB.BN2 from XXXB" & vbCrLf & _
        "    )"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "From table1 t1 Partition ((a1, a2, a3))" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = Any(" & vbCrLf & _
        "       select bpn2 from table2" & vbCrLf & _
        "    )"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql

    ' () 不能配对1
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "From XXXA t1 Partition ((a1, a2, a3)" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = Any(" & vbCrLf & _
        "       select XXXB.BN2 from XXXB" & vbCrLf & _
        "    )"

    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "From table1 t1 Partition ((a1, a2, a3)" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = Any(" & vbCrLf & _
        "       select bpn2 from table2" & vbCrLf & _
        "    )"

    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql

    ' TODO 暂时无法测试 ----- 完成【非标准 sql】解析后再做测试
    ' () 不能配对2
    'sql = "" & _
    '    "Select" & vbCrLf & _
    '    "    XXXA.AN1," & vbCrLf & _
    '    "From XXXA t1 Partition (a1, a2, a3))" & vbCrLf & _
    '    "Where" & vbCrLf & _
    '    "    XXXA.AN1 = Any(" & vbCrLf & _
    '    "       select XXXB.BN2 from XXXB" & vbCrLf & _
    '    "    )"
'
    'exceptedSql = "" & _
    '    "Select" & vbCrLf & _
    '    "    t1.apn1," & vbCrLf & _
    '    "From table1 t1 Partition (a1, a2, a3)" & vbCrLf & _
    '    "Where" & vbCrLf & _
    '    "    t1.apn1 = Any(" & vbCrLf & _
    '    "       select bpn2 from table2" & vbCrLf & _
    '    "    )"
'
    'translatedSql = translator.translateToPhySql(sql)
'
    ''Debug.Print translatedSql
    'Debug.Assert translatedSql = exceptedSql
End Sub

' select 测试: from + partition 不包含 ()
Private Sub test_SQLTranslator_select_partition_no_Parenthesis()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' () 能够配对
    Dim sql As String
    sql = "" & _
        "Select" & vbCrLf & _
        "    XXXA.AN1," & vbCrLf & _
        "From XXXA t1 Partition a1, a2, a3" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = Any(" & vbCrLf & _
        "       select XXXB.BN2 from XXXB" & vbCrLf & _
        "    )"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "From table1 t1 Partition a1, a2, a3" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = Any(" & vbCrLf & _
        "       select bpn2 from table2" & vbCrLf & _
        "    )"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)
End Sub

' select 测试: 获取 union 中的列名
Private Sub test_SQLTranslator_select_get_union_colname()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "select * from (" & vbCrLf & _
        "    select" & vbCrLf & _
        "        t1.AN1," & vbCrLf & _
        "        t1.AN2" & vbCrLf & _
        "    from XXXA t1" & vbCrLf & _
        "    union all" & vbCrLf & _
        "    select" & vbCrLf & _
        "        t2.AN1," & vbCrLf & _
        "        t2.AN2" & vbCrLf & _
        "    from XXXA t2" & vbCrLf & _
        ") t3" & vbCrLf & _
        "where t3.AN2 = '2'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "select * from (" & vbCrLf & _
        "    select" & vbCrLf & _
        "        t1.apn1," & vbCrLf & _
        "        t1.apn2" & vbCrLf & _
        "    from table1 t1" & vbCrLf & _
        "    union all" & vbCrLf & _
        "    select" & vbCrLf & _
        "        t2.apn1," & vbCrLf & _
        "        t2.apn2" & vbCrLf & _
        "    from table1 t2" & vbCrLf & _
        ") t3" & vbCrLf & _
        "where t3.apn2 = '2'"


    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

'========================================================
'========================================================
' update 测试
'========================================================
'========================================================

' update 测试: 基础组成结构测试
Private Sub test_SQLTranslator_update_base()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "update XXXA set AN1 = AN2 + 1 || AN1" & vbCrLf & _
        "where XXXA.AN1 = 'xxx'" & vbCrLf & _
        "order by XXXA.AN1, AN2" & vbCrLf & _
        "limit 123"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "update table1 set apn1 = apn2 + 1 || apn1" & vbCrLf & _
        "where table1.apn1 = 'xxx'" & vbCrLf & _
        "order by table1.apn1, apn2" & vbCrLf & _
        "limit 123"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' update 测试: 表别名测试
Private Sub test_SQLTranslator_update_table_aliasName()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "update XXXA as t1 set t1.AN1 = AN2 + 1 || XXXA.AN1" & vbCrLf & _
        "where XXXA.AN1 = 'xxx'" & vbCrLf & _
        "order by t1.AN1, AN2" & vbCrLf & _
        "limit 123"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "update table1 as t1 set t1.apn1 = apn2 + 1 || t1.apn1" & vbCrLf & _
        "where t1.apn1 = 'xxx'" & vbCrLf & _
        "order by t1.apn1, apn2" & vbCrLf & _
        "limit 123"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' update 测试: 多表测试: 真实表 + 子查询表
Private Sub test_SQLTranslator_update_multi_table()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "update XXXA," & vbCrLf & _
        "    (" & vbCrLf & _
        "        select BN1, BN2" & vbCrLf & _
        "        from XXXB" & vbCrLf & _
        "    ) as sub1" & vbCrLf & _
        "set AN1 = BN1 + 1 || sub1.BN2" & vbCrLf & _
        "where AN1 = 'xxx'" & vbCrLf & _
        "order by AN1, AN2, BN2" & vbCrLf & _
        "limit 123"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "update table1," & vbCrLf & _
        "    (" & vbCrLf & _
        "        select bpn1, bpn2" & vbCrLf & _
        "        from table2" & vbCrLf & _
        "    ) as sub1" & vbCrLf & _
        "set apn1 = bpn1 + 1 || sub1.bpn2" & vbCrLf & _
        "where apn1 = 'xxx'" & vbCrLf & _
        "order by apn1, apn2, bpn2" & vbCrLf & _
        "limit 123"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

'========================================================
'========================================================
' delete 测试
'========================================================
'========================================================
' delete 测试: 单表删除测试
Private Sub test_SQLTranslator_delete_single_table()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "DELETE LOW_PRIORITY QUICK IGNORE" & vbCrLf & _
        "FROM XXXA as t1" & vbCrLf & _
        "PARTITION (p1, p2, p3)" & vbCrLf & _
        "WHERE XXXA.AN1 = 'xxx'" & vbCrLf & _
        "And XXXA.AN2 = 'yyy'" & vbCrLf & _
        "ORDER BY XXXA.AN1, t1.AN2" & vbCrLf & _
        "LIMIT 2"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "DELETE LOW_PRIORITY QUICK IGNORE" & vbCrLf & _
        "FROM table1 as t1" & vbCrLf & _
        "PARTITION (p1, p2, p3)" & vbCrLf & _
        "WHERE t1.apn1 = 'xxx'" & vbCrLf & _
        "And t1.apn2 = 'yyy'" & vbCrLf & _
        "ORDER BY t1.apn1, t1.apn2" & vbCrLf & _
        "LIMIT 2"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

'========================================================
'========================================================
' insert 测试
'========================================================
'========================================================
' insert 测试: 基础测试
Private Sub test_SQLTranslator_insert_base()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "insert into XXXA" & vbCrLf & _
        "partition (xxx, yyy, zzz)" & vbCrLf & _
        "(AN1, AN2)" & vbCrLf & _
        "values ('xxx','zzzz')"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "insert into table1" & vbCrLf & _
        "partition (xxx, yyy, zzz)" & vbCrLf & _
        "(apn1, apn2)" & vbCrLf & _
        "values ('xxx','zzzz')"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' insert 测试: 全关键字测试
Private Sub test_SQLTranslator_insert_all_key()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "INSERT LOW_PRIORITY IGNORE" & vbCrLf & _
        "    INTO XXXA" & vbCrLf & _
        "    PARTITION (p1, p2, p3)" & vbCrLf & _
        "    (AN1, AN2)" & vbCrLf & _
        "    VALUE" & vbCrLf & _
        "    ('a1', 'b1')," & vbCrLf & _
        "    ('a2', 'b2')" & vbCrLf & _
        "    ON DUPLICATE KEY UPDATE AN1 = 'x', AN2 = 'y'"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "INSERT LOW_PRIORITY IGNORE" & vbCrLf & _
        "    INTO table1" & vbCrLf & _
        "    PARTITION (p1, p2, p3)" & vbCrLf & _
        "    (apn1, apn2)" & vbCrLf & _
        "    VALUE" & vbCrLf & _
        "    ('a1', 'b1')," & vbCrLf & _
        "    ('a2', 'b2')" & vbCrLf & _
        "    ON DUPLICATE KEY UPDATE apn1 = 'x', apn2 = 'y'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' insert 测试: ON DUPLICATE KEY UPDATE
Private Sub test_SQLTranslator_insert_on_duplicate()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "insert into XXXA" & vbCrLf & _
        "partition (xxx, yyy, zzz)" & vbCrLf & _
        "(AN1, AN2)" & vbCrLf & _
        "values ('xxx','zzzz')" & vbCrLf & _
        "ON DUPLICATE KEY UPDATE" & vbCrLf & _
        "AN1 = 'x1'," & vbCrLf & _
        "AN2 = 'y1'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "insert into table1" & vbCrLf & _
        "partition (xxx, yyy, zzz)" & vbCrLf & _
        "(apn1, apn2)" & vbCrLf & _
        "values ('xxx','zzzz')" & vbCrLf & _
        "ON DUPLICATE KEY UPDATE" & vbCrLf & _
        "apn1 = 'x1'," & vbCrLf & _
        "apn2 = 'y1'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' insert 测试: 子程序测试
Private Sub test_SQLTranslator_insert_subSelect()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "insert into XXXA" & vbCrLf & _
        "partition (xxx, yyy, zzz)" & vbCrLf & _
        "(AN1, AN2)" & vbCrLf & _
        "(select BN1, BN2 from XXXB)" & vbCrLf & _
        "ON DUPLICATE KEY UPDATE" & vbCrLf & _
        "AN1 = 'x1'," & vbCrLf & _
        "AN2 = 'y1'"


    Dim exceptedSql as String
    exceptedSql = "" & _
        "insert into table1" & vbCrLf & _
        "partition (xxx, yyy, zzz)" & vbCrLf & _
        "(apn1, apn2)" & vbCrLf & _
        "(select bpn1, bpn2 from table2)" & vbCrLf & _
        "ON DUPLICATE KEY UPDATE" & vbCrLf & _
        "apn1 = 'x1'," & vbCrLf & _
        "apn2 = 'y1'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

'========================================================
'========================================================
' truncate 测试
'========================================================
'========================================================

' truncate 测试
Private Sub test_SQLTranslator_truncate_base()
    Dim sql As String
    Dim exceptedSql as String
    Dim translatedSql As String
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2.1 truancate table name
    sql = "" & _
        "truncate table XXXB"

    exceptedSql = "" & _
        "truncate table table2"

    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql

    ' 2.2 truncate name
    sql = "" & _
        "truncate XXXB"

    exceptedSql = "" & _
        "truncate table2"

    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

'========================================================
'========================================================
' with 测试
'========================================================
'========================================================
' with 测试: 基础测试
Private Sub test_SQLTranslator_with_base()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "with wt1 As (" & vbCrLf & _
        "    select * from XXXA" & vbCrLf & _
        "), wt2(wt2c1, wt2c2) As (" & vbCrLf & _
        "    select * from XXXB" & vbCrLf & _
        ")" & vbCrLf & _
        "Select" & vbCrLf & _
        "    wt1.AN1," & vbCrLf & _
        "    wt2.BN1" & vbCrLf & _
        "From wt1 t1" & vbCrLf & _
        "left outer join wt2 t2 on" & vbCrLf & _
        "    wt1.AN1 = wt2.BN1" & vbCrLf & _
        "    And wt1.AN2 = wt2.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    wt1.AN1 = '123'" & vbCrLf & _
        "    And wt1.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "with wt1 As (" & vbCrLf & _
        "    select * from table1" & vbCrLf & _
        "), wt2(wt2c1, wt2c2) As (" & vbCrLf & _
        "    select * from table2" & vbCrLf & _
        ")" & vbCrLf & _
        "Select" & vbCrLf & _
        "    t1.apn1," & vbCrLf & _
        "    t2.wt2c1" & vbCrLf & _
        "From wt1 t1" & vbCrLf & _
        "left outer join wt2 t2 on" & vbCrLf & _
        "    t1.apn1 = t2.wt2c1" & vbCrLf & _
        "    And t1.apn2 = t2.wt2c2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 中包含 union
Private Sub test_SQLTranslator_with_from_union()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "with wt1(wt1c1, wt1c2, wt1c3) As (" & vbCrLf & _
        "    select * from XXXA" & vbCrLf & _
        "    union all" & vbCrLf & _
        "    select t2.*, t1.* from XXXC t1" & vbCrLf & _
        "    inner join XXXB t2" & vbCrLf & _
        "    on t1.CN1 = t2.BN1" & vbCrLf & _
        "), wt2(wt2c1, wt2c2) As (" & vbCrLf & _
        "    select * from XXXB" & vbCrLf & _
        ")" & vbCrLf & _
        "Select" & vbCrLf & _
        "    wt1.AN1," & vbCrLf & _
        "    wt1.CN1," & vbCrLf & _
        "    wt2.BN1" & vbCrLf & _
        "From wt1 t1" & vbCrLf & _
        "left outer join wt2 t2 on" & vbCrLf & _
        "    wt1.CN1 = wt2.BN1" & vbCrLf & _
        "    And wt1.AN2 = wt2.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    wt1.CN1 = '123'" & vbCrLf & _
        "    And wt1.AN2 = 'zzz'"


    Dim exceptedSql as String
    exceptedSql = "" & _
        "with wt1(wt1c1, wt1c2, wt1c3) As (" & vbCrLf & _
        "    select * from table1" & vbCrLf & _
        "    union all" & vbCrLf & _
        "    select t2.*, t1.* from table3 t1" & vbCrLf & _
        "    inner join table2 t2" & vbCrLf & _
        "    on t1.cpn1 = t2.bpn1" & vbCrLf & _
        "), wt2(wt2c1, wt2c2) As (" & vbCrLf & _
        "    select * from table2" & vbCrLf & _
        ")" & vbCrLf & _
        "Select" & vbCrLf & _
        "    t1.wt1c1," & vbCrLf & _
        "    t1.wt1c3," & vbCrLf & _
        "    t2.wt2c1" & vbCrLf & _
        "From wt1 t1" & vbCrLf & _
        "left outer join wt2 t2 on" & vbCrLf & _
        "    t1.wt1c3 = t2.wt2c1" & vbCrLf & _
        "    And t1.wt1c2 = t2.wt2c2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.wt1c3 = '123'" & vbCrLf & _
        "    And t1.wt1c2 = 'zzz'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 语句中的子查询中明确声明各个列的名称
Private Sub test_SQLTranslator_with_define_colname()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "with wt1(wt1c1, wt1c2, wt1c3) As (" & vbCrLf & _
        "    (select ta.AN1 As ax1, ta.AN2 from XXXA ta)" & vbCrLf & _
        "    union all" & vbCrLf & _
        "    (" & vbCrLf & _
        "        select" & vbCrLf & _
        "            t1.CN1, t1.CN2, t2.BN1" & vbCrLf & _
        "        from XXXC t1" & vbCrLf & _
        "        inner join XXXB t2" & vbCrLf & _
        "        on t1.CN1 = t2.BN1" & vbCrLf & _
        "    )" & vbCrLf & _
        "), wt2(wt2c1, wt2c2) As (" & vbCrLf & _
        "    select" & vbCrLf & _
        "        BN1, BN2" & vbCrLf & _
        "    from XXXB" & vbCrLf & _
        ")" & vbCrLf & _
        "Select" & vbCrLf & _
        "    wt1.ax1, wt1.BN1, wt2.BN1" & vbCrLf & _
        "From wt1 t1" & vbCrLf & _
        "left outer join wt2 t2 on" & vbCrLf & _
        "    wt1.CN1 = wt2.BN1" & vbCrLf & _
        "    And wt1.AN2 = wt2.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    wt1.CN1 = '123'" & vbCrLf & _
        "    And wt1.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "with wt1(wt1c1, wt1c2, wt1c3) As (" & vbCrLf & _
        "    (select ta.apn1 As ax1, ta.apn2 from table1 ta)" & vbCrLf & _
        "    union all" & vbCrLf & _
        "    (" & vbCrLf & _
        "        select" & vbCrLf & _
        "            t1.cpn1, t1.cpn2, t2.bpn1" & vbCrLf & _
        "        from table3 t1" & vbCrLf & _
        "        inner join table2 t2" & vbCrLf & _
        "        on t1.cpn1 = t2.bpn1" & vbCrLf & _
        "    )" & vbCrLf & _
        "), wt2(wt2c1, wt2c2) As (" & vbCrLf & _
        "    select" & vbCrLf & _
        "        bpn1, bpn2" & vbCrLf & _
        "    from table2" & vbCrLf & _
        ")" & vbCrLf & _
        "Select" & vbCrLf & _
        "    t1.wt1c1, t1.wt1c3, t2.wt2c1" & vbCrLf & _
        "From wt1 t1" & vbCrLf & _
        "left outer join wt2 t2 on" & vbCrLf & _
        "    t1.wt1c1 = t2.wt2c1" & vbCrLf & _
        "    And t1.wt1c2 = t2.wt2c2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.wt1c1 = '123'" & vbCrLf & _
        "    And t1.wt1c2 = 'zzz'"


    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: union 中的每个子查询中各自包含一个/多个 with
Private Sub test_SQLTranslator_with_used_in_union_every_subSelect()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "(" & vbCrLf & _
        "    with wt1(wt1c1, wt1c2, wt1c3) As (" & vbCrLf & _
        "        select * from XXXA" & vbCrLf & _
        "        union all" & vbCrLf & _
        "        select t2.*, t1.* from XXXC t1" & vbCrLf & _
        "        inner join XXXB t2" & vbCrLf & _
        "        on t1.CN1 = t2.BN1" & vbCrLf & _
        "    ), wt2(wt2c1, wt2c2) As (" & vbCrLf & _
        "        select * from XXXB" & vbCrLf & _
        "    )" & vbCrLf & _
        "    Select wt1.AN1 as c1, wt1.CN1 as c2, wt2.BN1 as c3" & vbCrLf & _
        "    From wt1 t1" & vbCrLf & _
        "    left outer join wt2 t2 on" & vbCrLf & _
        "        wt1.CN1 = wt2.BN1" & vbCrLf & _
        "        And wt1.AN2 = wt2.BN2" & vbCrLf & _
        "    Where" & vbCrLf & _
        "        wt1.CN1 = '123'" & vbCrLf & _
        "        And wt1.AN2 = 'zzz'" & vbCrLf & _
        ")" & vbCrLf & _
        "Union all" & vbCrLf & _
        "(" & vbCrLf & _
        "    with xt1(xt2c1, xt2c2) As (select BN1, BN2 from XXXB)" & vbCrLf & _
        "    Select t1.BN1, t1.xt2c2 from xt1 t1" & vbCrLf & _
        ")"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "(" & vbCrLf & _
        "    with wt1(wt1c1, wt1c2, wt1c3) As (" & vbCrLf & _
        "        select * from table1" & vbCrLf & _
        "        union all" & vbCrLf & _
        "        select t2.*, t1.* from table3 t1" & vbCrLf & _
        "        inner join table2 t2" & vbCrLf & _
        "        on t1.cpn1 = t2.bpn1" & vbCrLf & _
        "    ), wt2(wt2c1, wt2c2) As (" & vbCrLf & _
        "        select * from table2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    Select t1.wt1c1 as c1, t1.wt1c3 as c2, t2.wt2c1 as c3" & vbCrLf & _
        "    From wt1 t1" & vbCrLf & _
        "    left outer join wt2 t2 on" & vbCrLf & _
        "        t1.wt1c3 = t2.wt2c1" & vbCrLf & _
        "        And t1.wt1c2 = t2.wt2c2" & vbCrLf & _
        "    Where" & vbCrLf & _
        "        t1.wt1c3 = '123'" & vbCrLf & _
        "        And t1.wt1c2 = 'zzz'" & vbCrLf & _
        ")" & vbCrLf & _
        "Union all" & vbCrLf & _
        "(" & vbCrLf & _
        "    with xt1(xt2c1, xt2c2) As (select bpn1, bpn2 from table2)" & vbCrLf & _
        "    Select t1.xt2c1, t1.xt2c2 from xt1 t1" & vbCrLf & _
        ")"


    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with + union 嵌套的组合
Private Sub test_SQLTranslator_with_nest_union_nest()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "with wt1(w1c1, w1c2) As (" & vbCrLf & _
        "    select DN1, DN2 from XXXD" & vbCrLf & _
        ")" & vbCrLf & _
        "select t1.AN1, t2.DN2 from XXXA t1" & vbCrLf & _
        "inner join wt1 t2" & vbCrLf & _
        "on t1.AN1 = t2.DN1" & vbCrLf & _
        "union" & vbCrLf & _
        "(" & vbCrLf & _
        "    with xt(xtc1, xtc2) As (" & vbCrLf & _
        "        select t1.CN1, t2.DN2 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2" & vbCrLf & _
        "        on t1.CN1 = t2.DN1" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select xtc1, xtc2 from xt" & vbCrLf & _
        "    union" & vbCrLf & _
        "    select CN1, DN2 from xt" & vbCrLf & _
        ")" & vbCrLf & _
        "union" & vbCrLf & _
        "select"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "with wt1(w1c1, w1c2) As (" & vbCrLf & _
        "    select dpn1, dpn2 from table4" & vbCrLf & _
        ")" & vbCrLf & _
        "select t1.apn1, t2.w1c2 from table1 t1" & vbCrLf & _
        "inner join wt1 t2" & vbCrLf & _
        "on t1.apn1 = t2.w1c1" & vbCrLf & _
        "union" & vbCrLf & _
        "(" & vbCrLf & _
        "    with xt(xtc1, xtc2) As (" & vbCrLf & _
        "        select t1.cpn1, t2.w1c2 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2" & vbCrLf & _
        "        on t1.cpn1 = t2.w1c1" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select xtc1, xtc2 from xt" & vbCrLf & _
        "    union" & vbCrLf & _
        "    select xtc1, xtc2 from xt" & vbCrLf & _
        ")" & vbCrLf & _
        "union" & vbCrLf & _
        "select"
    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: 多个sql 中的 with
Private Sub test_SQLTranslator_with_multi_sql()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "(select t1.AN1, t2.DN2 from XXXA t1" & vbCrLf & _
        "inner join wt1 t2" & vbCrLf & _
        "on t1.AN1 = t2.DN1);" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (" & vbCrLf & _
        "    select DN1, DN2 from XXXD" & vbCrLf & _
        ")" & vbCrLf & _
        "select t1.AN1, t2.DN2 from XXXA t1" & vbCrLf & _
        "inner join wt1 t2" & vbCrLf & _
        "on t1.AN1 = t2.DN1" & vbCrLf & _
        "union" & vbCrLf & _
        "(" & vbCrLf & _
        "    with xt(xtc1, xtc2) As (" & vbCrLf & _
        "        select t1.CN1, t2.DN2 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2" & vbCrLf & _
        "        on t1.CN1 = t2.DN1" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select xtc1, xtc2 from xt" & vbCrLf & _
        "    union" & vbCrLf & _
        "    select CN1, DN2 from xt" & vbCrLf & _
        ")" & vbCrLf & _
        "union" & vbCrLf & _
        "select"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" & vbCrLf & _
        "(select t1.apn1, t2.w1c2 from table1 t1" & vbCrLf & _
        "inner join wt1 t2" & vbCrLf & _
        "on t1.apn1 = t2.w1c1);" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (" & vbCrLf & _
        "    select dpn1, dpn2 from table4" & vbCrLf & _
        ")" & vbCrLf & _
        "select t1.apn1, t2.w1c2 from table1 t1" & vbCrLf & _
        "inner join wt1 t2" & vbCrLf & _
        "on t1.apn1 = t2.w1c1" & vbCrLf & _
        "union" & vbCrLf & _
        "(" & vbCrLf & _
        "    with xt(xtc1, xtc2) As (" & vbCrLf & _
        "        select t1.cpn1, t2.w1c2 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2" & vbCrLf & _
        "        on t1.cpn1 = t2.w1c1" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select xtc1, xtc2 from xt" & vbCrLf & _
        "    union" & vbCrLf & _
        "    select xtc1, xtc2 from xt" & vbCrLf & _
        ")" & vbCrLf & _
        "union" & vbCrLf & _
        "select"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 的子查询中的列全部是字母量列
Private Sub test_SQLTranslator_with_Literal_col()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with RECURSIVE wt1 (w1c1) As (" & vbCrLf & _
        "    SELECT 1" & vbCrLf & _
        "    UNION ALL" & vbCrLf & _
        "    SELECT AN1 + 1 FROM XXXA WHERE AN1 < 5" & vbCrLf & _
        ")" & vbCrLf & _
        "SELECT w1c1 FROM wt1;"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with RECURSIVE wt1 (w1c1) As (" & vbCrLf & _
        "    SELECT 1" & vbCrLf & _
        "    UNION ALL" & vbCrLf & _
        "    SELECT apn1 + 1 FROM table1 WHERE apn1 < 5" & vbCrLf & _
        ")" & vbCrLf & _
        "SELECT w1c1 FROM wt1;"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with + update
Private Sub test_SQLTranslator_with_in_update()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "update XXXA, wt1" & vbCrLf & _
        "set XXXA.AN1 = DN2 + 1 || wt1.DN2" & vbCrLf & _
        "where AN1 = 'xxx'" & vbCrLf & _
        "and DN1 = 'yyyy'" & vbCrLf & _
        "order by AN1, AN2, DN1" & vbCrLf & _
        "limit 123"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" & vbCrLf & _
        "update table1, wt1" & vbCrLf & _
        "set table1.apn1 = w1c2 + 1 || wt1.w1c2" & vbCrLf & _
        "where apn1 = 'xxx'" & vbCrLf & _
        "and w1c1 = 'yyyy'" & vbCrLf & _
        "order by apn1, apn2, w1c1" & vbCrLf & _
        "limit 123"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with + delete
Private Sub test_SQLTranslator_with_in_delete()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "delete XXXA, wt1" & vbCrLf & _
        "from XXXA" & vbCrLf & _
        "inner join wt1" & vbCrLf & _
        "on XXXA.AN1 = wt1.DN1" & vbCrLf & _
        "where AN1 = 'xxxxx'" & vbCrLf & _
        "and AN2=DN2;"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" & vbCrLf & _
        "delete table1, wt1" & vbCrLf & _
        "from table1" & vbCrLf & _
        "inner join wt1" & vbCrLf & _
        "on table1.apn1 = wt1.w1c1" & vbCrLf & _
        "where apn1 = 'xxxxx'" & vbCrLf & _
        "and apn2=w1c2;"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 在 where 的子查询中
Private Sub test_SQLTranslator_with_in_where_subselect()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "select *" & vbCrLf & _
        "from XXXA" & vbCrLf & _
        "where AN1 = (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select CN1, DN1 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.CN2 = t2.DN2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select max(CN1) from wt2" & vbCrLf & _
        ")"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" &  vbCrLf & _
        "select *" &  vbCrLf & _
        "from table1" &  vbCrLf & _
        "where apn1 = (" &  vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" &  vbCrLf & _
        "        select cpn1, w1c1 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.cpn2 = t2.w1c2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select max(w2c1) from wt2" &  vbCrLf & _
        ")"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 在 join 的子查询中
Private Sub test_SQLTranslator_with_in_join_subselect()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "select t1.AN2, t2.BN2" & vbCrLf & _
        "from XXXA t1" & vbCrLf & _
        "inner join XXXB t2" & vbCrLf & _
        "on t1.AN1 = t2.BN1" & vbCrLf & _
        "and t2.BN2 = (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select CN1, DN1 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.CN2 = t2.DN2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select max(CN1) from wt2" & vbCrLf & _
        ")" & vbCrLf & _
        "where AN1 = (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select CN1, DN1 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.CN2 = t2.DN2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select max(CN1) from wt2" & vbCrLf & _
        ")"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" &  vbCrLf & _
        "select t1.apn2, t2.bpn2" &  vbCrLf & _
        "from table1 t1" & vbCrLf & _
        "inner join table2 t2" & vbCrLf & _
        "on t1.apn1 = t2.bpn1" & vbCrLf & _
        "and t2.bpn2 = (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" &  vbCrLf & _
        "        select cpn1, w1c1 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.cpn2 = t2.w1c2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select max(w2c1) from wt2" &  vbCrLf & _
        ")" & vbCrLf & _
        "where apn1 = (" &  vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" &  vbCrLf & _
        "        select cpn1, w1c1 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.cpn2 = t2.w1c2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select max(w2c1) from wt2" &  vbCrLf & _
        ")"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 在 join 子查询表中
Private Sub test_SQLTranslator_with_in_join_table()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "select t1.AN2, t2.CN1" & vbCrLf & _
        "from XXXA t1" & vbCrLf & _
        "inner join (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select CN1, DN1 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.CN2 = t2.DN2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select CN1, DN1 from wt2" & vbCrLf & _
        ") t2" & vbCrLf & _
        "on t1.AN1 = t2.CN1" & vbCrLf & _
        "and t2.DN1 = 'xxx'"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" & vbCrLf & _
        "select t1.apn2, t2.w2c1" & vbCrLf & _
        "from table1 t1" & vbCrLf & _
        "inner join (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select cpn1, w1c1 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.cpn2 = t2.w1c2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select w2c1, w2c2 from wt2" & vbCrLf & _
        ") t2" & vbCrLf & _
        "on t1.apn1 = t2.w2c1" & vbCrLf & _
        "and t2.w2c2 = 'xxx'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 在 from 子查询表中
Private Sub test_SQLTranslator_with_in_from_table()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "select t1.CN1, t2.BN1" & vbCrLf & _
        "from (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select CN1, DN1 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.CN2 = t2.DN2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select CN1, DN1 from wt2" & vbCrLf & _
        ") t1" & vbCrLf & _
        "inner join XXXB t2" & vbCrLf & _
        "on t1.CN1 = t2.BN1" & vbCrLf & _
        "and t1.DN1 = 'xxx'"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" & vbCrLf & _
        "select t1.w2c1, t2.bpn1" & vbCrLf & _
        "from (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select cpn1, w1c1 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.cpn2 = t2.w1c2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select w2c1, w2c2 from wt2" & vbCrLf & _
        ") t1" & vbCrLf & _
        "inner join table2 t2" & vbCrLf & _
        "on t1.w2c1 = t2.bpn1" & vbCrLf & _
        "and t1.w2c2 = 'xxx'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 在 join 的子查询中
Private Sub test_SQLTranslator_with_in_exists()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "select t1.AN2, t2.BN2" & vbCrLf & _
        "from XXXA t1" & vbCrLf & _
        "inner join XXXB t2" & vbCrLf & _
        "on t1.AN1 = t2.BN1" & vbCrLf & _
        "and t2.BN2 exists (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select CN1, DN1 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.CN2 = t2.DN2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select CN1 from wt2" & vbCrLf & _
        ")" & vbCrLf & _
        "where AN1 exists (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select CN1, DN1 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.CN2 = t2.DN2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select CN1 from wt2" & vbCrLf & _
        ")"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" &  vbCrLf & _
        "select t1.apn2, t2.bpn2" &  vbCrLf & _
        "from table1 t1" & vbCrLf & _
        "inner join table2 t2" & vbCrLf & _
        "on t1.apn1 = t2.bpn1" & vbCrLf & _
        "and t2.bpn2 exists (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" &  vbCrLf & _
        "        select cpn1, w1c1 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.cpn2 = t2.w1c2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select w2c1 from wt2" &  vbCrLf & _
        ")" & vbCrLf & _
        "where apn1 exists (" &  vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" &  vbCrLf & _
        "        select cpn1, w1c1 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.cpn2 = t2.w1c2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select w2c1 from wt2" &  vbCrLf & _
        ")"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 在筛选列的子查询中
Private Sub test_SQLTranslator_with_in_subselect_col()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "select t1.AN2," & vbCrLf & _
        "(" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select CN1, DN1 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.CN2 = t2.DN2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select CN1 from wt2" & vbCrLf & _
        ")" & vbCrLf & _
        "from XXXA t1"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" & vbCrLf &  _
        "select t1.apn2," & vbCrLf &  _
        "(" & vbCrLf &  _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf &  _
        "        select cpn1, w1c1 from table3 t1" & vbCrLf &  _
        "        inner join wt1 t2 on t1.cpn2 = t2.w1c2" & vbCrLf &  _
        "    )" & vbCrLf &  _
        "    select w2c1 from wt2" & vbCrLf &  _
        ")" & vbCrLf &  _
        "from table1 t1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' with 测试: with 在 in 条件的子查询中
Private Sub test_SQLTranslator_with_inCondition()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select DN1, DN2 from XXXD)" & vbCrLf & _
        "select *" & vbCrLf & _
        "from XXXA" & vbCrLf & _
        "where AN1 not in (" & vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" & vbCrLf & _
        "        select CN1, DN1 from XXXC t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.CN2 = t2.DN2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select max(CN1) from wt2" & vbCrLf & _
        ")"

    Dim exceptedSql as String
    exceptedSql = "" & vbCrLf & _
        "with wt1(w1c1, w1c2) As (select dpn1, dpn2 from table4)" &  vbCrLf & _
        "select *" &  vbCrLf & _
        "from table1" &  vbCrLf & _
        "where apn1 not in (" &  vbCrLf & _
        "    with wt2(w2c1, w2c2) As (" &  vbCrLf & _
        "        select cpn1, w1c1 from table3 t1" & vbCrLf & _
        "        inner join wt1 t2 on t1.cpn2 = t2.w1c2" & vbCrLf & _
        "    )" & vbCrLf & _
        "    select max(w2c1) from wt2" &  vbCrLf & _
        ")"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

'========================================================
'========================================================
' 非标准 sql 测试
'========================================================
'========================================================
' 非标准 sql 测试: 基础转换
Private Sub test_SQLTranslator_AbnormalSnippets_base()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "    XXXA.AN1," & vbCrLf & _
        "    XXXB.BN1" & vbCrLf & _
        "From XXXA t1" & vbCrLf & _
        "left outer join XXXB t2 on " & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "Group by XXXA.AN1, XXXB.BN1" & vbCrLf & _
        "Having XXXA.AN1 > XXXB.BN1" & vbCrLf & _
        "       And XXXB.BN1 between XXXA.AN1 And XXXA.AN2" & vbCrLf & _
        "Order by XXXA.AN1 desc, XXXB.BN1 asc, XXXB.BN1"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "    t1.apn1," & vbCrLf & _
        "    t2.bpn1" & vbCrLf & _
        "From table1 t1" & vbCrLf & _
        "left outer join table2 t2 on " & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "Group by t1.apn1, t2.bpn1" & vbCrLf & _
        "Having t1.apn1 > t2.bpn1" & vbCrLf & _
        "       And t2.bpn1 between t1.apn1 And t1.apn2" & vbCrLf & _
        "Order by t1.apn1 desc, t2.bpn1 asc, t2.bpn1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

' 非标准 sql 测试: 从 join 开始
Private Sub test_SQLTranslator_AbnormalSnippets_join_start()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "left outer join XXXB t2 on " & vbCrLf & _
        "    XXXA.AN1 = XXXB.BN1" & vbCrLf & _
        "    And XXXA.AN2 = XXXB.BN2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'" & vbCrLf & _
        "Group by XXXA.AN1, XXXB.BN1" & vbCrLf & _
        "Having XXXA.AN1 > XXXB.BN1" & vbCrLf & _
        "       And XXXB.BN1 between XXXA.AN1 And XXXA.AN2" & vbCrLf & _
        "Order by XXXA.AN1 desc, XXXB.BN1 asc, XXXB.BN1"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "left outer join table2 t2 on " & vbCrLf & _
        "    t1.apn1 = t2.bpn1" & vbCrLf & _
        "    And t1.apn2 = t2.bpn2" & vbCrLf & _
        "Where" & vbCrLf & _
        "    t1.apn1 = '123'" & vbCrLf & _
        "    And t1.apn2 = 'zzz'" & vbCrLf & _
        "Group by t1.apn1, t2.bpn1" & vbCrLf & _
        "Having t1.apn1 > t2.bpn1" & vbCrLf & _
        "       And t2.bpn1 between t1.apn1 And t1.apn2" & vbCrLf & _
        "Order by t1.apn1 desc, t2.bpn1 asc, t2.bpn1"

    ' 通过 tips 指定 from 表
    Dim tips As String
    tips = "XXXA t1"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql, tips)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

'========================================================
'========================================================
' tips 测试
'========================================================
'========================================================
' tips 测试: 只有表名，不设置表别名
Private Sub test_SQLTranslator_tips_no_table_aliasName()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "Where" & vbCrLf & _
        "    XXXA.AN1 = '123'" & vbCrLf & _
        "    And XXXA.AN2 = 'zzz'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "Where" & vbCrLf & _
        "    apn1 = '123'" & vbCrLf & _
        "    And apn2 = 'zzz'"

    ' 通过 tips 指定 from 表
    Dim tips As String
    tips = "XXXA"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql, tips)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub

'========================================================
'========================================================
' sql 常量测试
'========================================================
'========================================================
' sql 常量测试 测试: 基础识别测试
Private Sub test_SQLTranslator_sqlConst_base()
    ' 1. 初始化 entity管理器 + sql 翻译器
    Dim translator As SqlTranslator
    Set translator = createSQLTranslator

    ' 2. 需要翻译的sql及其翻译后的 sql
    Dim sql As String
    sql = "" & _
        "update XXXB" & vbCrLf & _
        "set" & vbCrLf & _
        "    BN1 = (" & vbCrLf & _
        "        case BN1" & vbCrLf & _
        "        when '0' then To_CHAR(CURRENT_TIMESTAMP, 'yyyyMMdd')" & vbCrLf & _
        "        else 'xxx'" & vbCrLf & _
        "        end" & vbCrLf & _
        "    )," & vbCrLf & _
        "    BN2 = TO_CHAR(CURRENT_TIMESTAMP, 'yyyyMMdd')" & vbCrLf & _
        "where" & vbCrLf & _
        "    TO_CHAR(CURRENT_TIMESTAMP, 'yyyyMMdd') = '20200102'"

    Dim exceptedSql as String
    exceptedSql = "" & _
        "update table2" & vbCrLf & _
        "set" & vbCrLf & _
        "    bpn1 = (" & vbCrLf & _
        "        case bpn1" & vbCrLf & _
        "        when '0' then To_CHAR(CURRENT_TIMESTAMP, 'yyyyMMdd')" & vbCrLf & _
        "        else 'xxx'" & vbCrLf & _
        "        end" & vbCrLf & _
        "    )," & vbCrLf & _
        "    bpn2 = TO_CHAR(CURRENT_TIMESTAMP, 'yyyyMMdd')" & vbCrLf & _
        "where" & vbCrLf & _
        "    TO_CHAR(CURRENT_TIMESTAMP, 'yyyyMMdd') = '20200102'"

    Dim translatedSql As String
    translatedSql = translator.translateToPhySql(sql)

    'Debug.Print translatedSql
    Debug.Assert translatedSql = exceptedSql
End Sub